using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;

namespace eBirdAttend
{
    public partial class DateWiseAttendance : UserControl
    {
        public string strCon = Properties.Settings.Default["ConString"].ToString();
        SqlConnection sqlCon = new SqlConnection();
        SqlCommand sqlCmd = new SqlCommand();
        SqlCommand sqlCmd1 = new SqlCommand();
        string strCaption = "";

        public DateWiseAttendance()
        {
            InitializeComponent();
            sqlCon.ConnectionString = strCon;
            sqlCmd.Connection = sqlCon;
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            if (dataGridView1.SelectedRows.Count == 0)
            { MessageBox.Show("Please select a row for edit"); return; }

            if ((dataGridView1.RowCount - 1) == dataGridView1.SelectedRows[0].Index)
            {
                MessageBox.Show("No content to edit");
                EditGroupBox.Visible = false;
                listgroupBox.Visible = true;
                return;
            }
            strCaption = "EDIT";
            EditGroupBox.Text = "EDIT";
            EditGroupBox.Visible = true;
            listgroupBox.Visible = false;

            if (dataGridView1.SelectedRows[0].Cells[4].Value.ToString() == "Present")
            {
                txtEmpId.Text = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
                datePicker.Value = Convert.ToDateTime(dataGridView1.SelectedRows[0].Cells[1].Value.ToString());
                txtFirstName.Text = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
                txtLastName.Text = dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
                txtHrs.Text = dataGridView1.SelectedRows[0].Cells[5].Value.ToString();
                txtMinutes.Text = dataGridView1.SelectedRows[0].Cells[6].Value.ToString();

                DateTime StartTime = Convert.ToDateTime(dataGridView1.SelectedRows[0].Cells[7].Value.ToString());

                if (StartTime.Hour > 12)
                {
                    cmbStartHr.Text = Convert.ToString(StartTime.Hour - 12);
                }
                else
                {
                    cmbStartHr.Text = StartTime.Hour.ToString();
                }
                cmbStartMin.Text = StartTime.Minute.ToString();
                cmbStartSec.Text = StartTime.Second.ToString();
                if (dataGridView1.SelectedRows[0].Cells[7].Value.ToString().Substring(dataGridView1.SelectedRows[0].Cells[7].Value.ToString().Length - 2, 2) == "PM")
                {
                    cmbStartAMPM.SelectedIndex = 1;
                }
                else
                {
                    cmbStartAMPM.SelectedIndex = 0;
                }
                DateTime EndTime = Convert.ToDateTime(dataGridView1.SelectedRows[0].Cells[8].Value.ToString());
                if (EndTime.Hour > 12)
                {
                    cmbEndHr.Text = Convert.ToString(EndTime.Hour - 12);
                }
                else
                {
                    cmbEndHr.Text = EndTime.Hour.ToString();
                }
                cmbEndMin.Text = EndTime.Minute.ToString();
                cmbEndSec.Text = EndTime.Second.ToString();
                if (dataGridView1.SelectedRows[0].Cells[8].Value.ToString().Substring(dataGridView1.SelectedRows[0].Cells[8].Value.ToString().Length - 2, 2) == "PM")
                {
                    cmbEndAMPM.SelectedIndex = 1;
                }
                else
                {
                    cmbEndAMPM.SelectedIndex = 0;
                }
                cmbStatus.SelectedIndex = 1;

                string _strOvertime = dataGridView1.SelectedRows[0].Cells[9].Value.ToString();
                DateTime dtOvertime = Convert.ToDateTime(_strOvertime);
                txtOverTime.Text = (dtOvertime.Hour * 60 + dtOvertime.Minute).ToString();
                string _strLateness = dataGridView1.SelectedRows[0].Cells[10].Value.ToString();
                DateTime dtLateness = Convert.ToDateTime(_strLateness);
                txtLateness.Text = (dtLateness.Hour * 60 + dtLateness.Minute).ToString();
                //txtOverTime.Text =  dataGridView1.SelectedRows[0].Cells[9].Value.ToString();
                //txtLateness.Text = dataGridView1.SelectedRows[0].Cells[10].Value.ToString();
                txtTotWrkedMinutes.Text = dataGridView1.SelectedRows[0].Cells[11].Value.ToString();
                lblRecrdNo.Text = dataGridView1.SelectedRows[0].Cells[16].Value.ToString();
                datePicker.Focus();
            }
            else if (dataGridView1.SelectedRows[0].Cells[4].Value.ToString() == "Absent")
            {
                txtEmpId.Text = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
                datePicker.Value = Convert.ToDateTime(dataGridView1.SelectedRows[0].Cells[1].Value.ToString());
                txtFirstName.Text = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
                txtLastName.Text = dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
                cmbStatus.SelectedIndex = 0;
                ControlsEnabled(false);                
                cmbStatus.SelectedIndexChanged += new EventHandler(cmbStatus_SelectedIndexChanged);
               
            }
           
        }

        private void ControlsEnabled(bool bEnable)
        {
            cmbStartHr.Enabled = bEnable;
            cmbStartMin.Enabled = bEnable;
            cmbStartSec.Enabled = bEnable;
            cmbStartAMPM.Enabled = bEnable;
            cmbEndHr.Enabled = bEnable;
            cmbEndMin.Enabled = bEnable;
            cmbEndSec.Enabled = bEnable;
            cmbEndAMPM.Enabled = bEnable;
            txtHrs.Enabled = bEnable;
            txtMinutes.Enabled = bEnable;
            txtOverTime.Enabled = bEnable;
            txtLateness.Enabled = bEnable;
            txtTotWrkedMinutes.Enabled = bEnable;
        }

        void cmbStatus_SelectedIndexChanged(object sender, EventArgs e)
        {
            //If the present status only need to enable the textboxes and controls
            if (cmbStatus.SelectedIndex == 0)  //Absent
                ControlsEnabled(false);
            else if (cmbStatus.SelectedIndex == 1)  //Present
                ControlsEnabled(true);
            else                                         //Leave
                ControlsEnabled(false);                
        }

        private void DateWiseAttendance_Load(object sender, EventArgs e)
        {
            EditGroupBox.Visible = false;
            listgroupBox.Visible = false;
            
            departmentDataset.Clear();
            departmentTableAdapter1.Fill(departmentDataset.Department);

            DataTable table1 = new DataTable("Table1");

            table1 = departmentDataset.Tables[0].Clone();

            // Add some rows.
            DataRow row;
            row = table1.NewRow();
            row["depid"] = 0;
            row["depname"] = "[Select]";
            row["depcmpid"] = "1";
            row["dephod"] = "Select";
            table1.Rows.Add(row);

            // Accept changes.
            table1.AcceptChanges();

            departmentDataset.Tables[0].Constraints.Clear();
            DataTable table2 = departmentDataset.Tables[0];

            table1.Merge(table2);

            cmbDept.DataSource = table1;
            cmbDept.DisplayMember = "depname";
            cmbDept.ValueMember = "depid";         

            cmbRange.SelectedIndex = 0;
            cmbDept.SelectedIndex = 0;

            txtEmpName.Focus();

            ArrayList Status = new ArrayList();
            Status.Add(new AddValue("Absent", 0));
            Status.Add(new AddValue("Present", 1));
            Status.Add(new AddValue("Leave", 2));

            this.cmbStatus.DataSource = Status;
            this.cmbStatus.DisplayMember = "Display";
            this.cmbStatus.ValueMember = "Value";

            cmbStatus.SelectedIndex = 0;

            
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            try
            {
                string strWhere = "";
                string strStartDate = dateStartDate.Value.Date.ToString("MM/dd/yyyy");
                string strEndDate = dateEndDate.Value.Date.ToString("MM/dd/yyyy");

                int dateStatus = -1;
                if (cmbRange.SelectedIndex == 0)
                {
                    MessageBox.Show("Select the date condtion");
                    cmbRange.Focus();
                    return;
                }
                else
                {
                    if (cmbRange.SelectedIndex == 1)
                    {
                        dateStatus = 2; //between
                        strWhere = "AttnDate >= '" + strStartDate.ToString() + "' and AttnDate <='" + strEndDate.ToString() + "'";
                    }
                    else if (cmbRange.SelectedIndex == 2)
                    {
                        dateStatus = 3; //less than equal to
                        strWhere = "AttnDate <= '" + strStartDate.ToString() + "' ";
                    }
                    else if (cmbRange.SelectedIndex == 3)
                    {
                        dateStatus = 4; //greater than equal to
                        strWhere = "AttnDate >= '" + strStartDate.ToString() + "' ";
                    }
                }
                if (txtEmpName.Text != "")
                {
                    strWhere += " and lower(empfirstname) like '%" + txtEmpName.Text.ToLower() + "%'";
                }

                if (cmbDept.SelectedIndex != 0)
                {
                    strWhere = strWhere + " and EmpDeptId = " + cmbDept.SelectedValue.ToString();
                }

                sqlCon.Open();
                sqlCmd.CommandText = "SELECT  EmpId,AttnDate AS Date,EmpFirstName,EmpLastName, "
                           + "PresentText as Status,dtHours as Hours,dtMinutes as Minutes,"
                           + "dtFirstInTime as InTime,dtLastOutTime as OutTime,"
                           + "[OverTime(Hrs:Min)],[Lateness(Hrs:Min)],"
                           + "dtTotWorkedMin as TotalTime,"
                           + "ShName AS Shift, Description AS DayStatus,"
                           + " DepName AS Department, BrName AS Branch, "
                           + " RecordNo FROM AttendanceReportView";


                if (strWhere != "")
                {
                    strWhere = " where " + strWhere;
                    sqlCmd.CommandText += strWhere;
                }

                sqlCmd.CommandText += "order by [Date] ";

                sqlCmd.CommandType = CommandType.Text;
                sqlCmd.Parameters.Clear();

                sqlCmd.ExecuteNonQuery();
                DataSet ds = new DataSet();
                SqlDataAdapter sqlAdap = new SqlDataAdapter(sqlCmd);
                sqlAdap.Fill(ds);
                dataGridView1.DataSource = ds;
                dataGridView1.DataMember = ds.Tables[0].ToString();
                dataGridView1.Columns[0].Visible = false;
                sqlCon.Close();
                listgroupBox.Visible = true;
            }
            catch (Exception ex)
            {
            }
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            strCaption = "ADD";
            EditGroupBox.Text = "NEW ENTRY";
            EditGroupBox.Visible = true;
            txtEmpId.Visible = false;
            cmbEmpid.Visible = true;
            FillEmployeeId();
            cmbEmpid.SelectedIndex = 0;
            listgroupBox.Visible = false;

            cmbStartAMPM.SelectedIndex = 0;
            cmbEndAMPM.SelectedIndex = 0;
            cmbStartHr.SelectedIndex = 0;
            cmbStartMin.SelectedIndex = 0;
            cmbStartSec.SelectedIndex = 0;
            cmbEndHr.SelectedIndex = 0;
            cmbEndMin.SelectedIndex = 0;
            cmbEndSec.SelectedIndex = 0;

            cmbEmpid.Focus();
        }

        public void FillEmployeeId()
        {
            sqlCon.Open();
            sqlCmd.CommandText = "SELECT convert(varchar,EmpId) as EmpId FROM  EmployDetails";           
            sqlCmd.CommandType = CommandType.Text;
            sqlCmd.Parameters.Clear();

            sqlCmd.ExecuteNonQuery();
            DataSet ds = new DataSet();
            SqlDataAdapter sqlAdap = new SqlDataAdapter(sqlCmd);
            sqlAdap.Fill(ds);

            DataTable table1 = new DataTable("Table1");

            table1 = ds.Tables[0].Clone();

            // Add some rows.
            DataRow row;
            row = table1.NewRow();
            row["EmpId"] = "[Select]";   
            table1.Rows.Add(row);

            // Accept changes.
            table1.AcceptChanges();          
            DataTable table2 = ds.Tables[0];

            table1.Merge(table2);

            cmbEmpid.DataSource = table1;
            cmbEmpid.DisplayMember = "EmpId";
            cmbEmpid.ValueMember = "EmpId";          
            sqlCon.Close();
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            DateTime StartDate;
            DateTime EndDate;
            String strAMPM = "";
            if (cmbStartAMPM.SelectedIndex == 0)
            {
                strAMPM = "AM";
            }
            else
            {
                strAMPM = "PM";
            }

            string strStartDate = cmbStartHr.Text + ":" + cmbStartMin.Text + ":" + cmbStartSec.Text +  strAMPM;
            StartDate = Convert.ToDateTime(strStartDate);

            if (cmbEndAMPM.SelectedIndex == 0)
            {
                strAMPM = "AM";
            }
            else
            {
                strAMPM = "PM";
            }

            string strEndDate = cmbEndHr.Text + ":" + cmbEndMin.Text + ":" + cmbEndSec.Text + strAMPM;
            EndDate = Convert.ToDateTime(strEndDate);

            //add a new entry
            if (strCaption == "ADD")
            {
                EditGroupBox.Visible = false;

                while (dataGridView1.Rows.Count > 1)
                    dataGridView1.Rows.RemoveAt(0);
                sqlCmd = new SqlCommand();
                sqlCmd.Parameters.Clear();
                sqlCmd.CommandText = "insert into DateWiseAttnData (dtEmpId,dtStatus,dtDate,dtHours,dtMinutes,dtFirstInTime,dtLastOutTime,dtOverTimeMin,dtLatenessMin,dtTotWorkedMin) values('" 
                                     + cmbEmpid.SelectedValue.ToString() + "','" + "1','"
                                     + datePicker.Value.Date.ToString("MM/dd/yyyy HH:mm:ss") + "','" + txtHrs.Text + "','" + txtMinutes.Text
                                     + "','" + Convert.ToDateTime(StartDate).ToString("MM/dd/yyyy HH:mm:ss") + "','"
                                     + Convert.ToDateTime(EndDate).ToString("MM/dd/yyyy HH:mm:ss") + "','"
                                     + txtOverTime.Text + "','"
                                     + txtLateness.Text + "','"
                                     + txtTotWrkedMinutes.Text + "')";
                sqlCmd.Connection = sqlCon;
                sqlCon.Open();
                sqlCmd.ExecuteNonQuery();
                sqlCon.Close();

                SqlDataReader reader;
                sqlCmd = new SqlCommand();
                sqlCmd.Parameters.Clear();
                sqlCmd.CommandText = "select max(recordno) from datewiseattndata";
                sqlCmd.Connection = sqlCon;
                sqlCon.Open();
                int recordno = 0;
                reader = sqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    recordno = Convert.ToInt32(reader[0].ToString());
                }
                sqlCon.Close();

                //also insert the same entry to the attendance master
                sqlCmd = new SqlCommand();
                sqlCmd.Parameters.Clear();
                sqlCmd.CommandText = "insert into AttentanceReportMaster (EmpId,RecordNo,description,AttnDate,DayType,Present,PresentText) values('"
                                     + cmbEmpid.SelectedValue.ToString() + "','" + recordno + "','" + " No description " + "','"
                                     + datePicker.Value.Date.ToString("MM/dd/yyyy HH:mm:ss") + "','" + "0" + "','" + "1"
                                     + "','" + "Present" + "')";
                sqlCmd.Connection = sqlCon;
                sqlCon.Open();
                sqlCmd.ExecuteNonQuery();
                sqlCon.Close();               

            }
            //edit an entry
            else if (strCaption == "EDIT")
            {
                EditGroupBox.Visible = false;
                while (dataGridView1.Rows.Count > 1)
                    dataGridView1.Rows.RemoveAt(0);
                sqlCmd = new SqlCommand();
                sqlCmd1 = new SqlCommand();
                
                sqlCmd.Parameters.Clear();
                if (cmbStatus.SelectedIndex == 0) //Absent
                {
                    sqlCmd.CommandText = "delete from DateWiseAttnData where recordno='"
                                       + lblRecrdNo.Text + "'";
                    sqlCmd1.CommandText = "update AttentanceReportMaster set Present = 0 , PresentText='Absent' where recordno='"
                                       + lblRecrdNo.Text + "'"; 
                }
                else if (cmbStatus.SelectedIndex == 1) //Present
                {
                    sqlCmd.CommandText = "update  DateWiseAttnData set dtDate='" + datePicker.Value.Date.ToString("MM/dd/yyyy HH:mm:ss")
                                        + "',dtHours='" + txtHrs.Text
                                        + "',dtMinutes='" + txtMinutes.Text
                                        + "',dtFirstInTime='" + Convert.ToDateTime(StartDate).ToString("MM/dd/yyyy HH:mm:ss")
                                        + "' ,dtLastOutTime='" + Convert.ToDateTime(EndDate).ToString("MM/dd/yyyy HH:mm:ss")
                                        + "' ,dtOverTimeMin='" + txtOverTime.Text
                                        + "',dtLatenessMin='"
                                        + txtLateness.Text
                                        + "' ,dtTotWorkedMin='" + txtTotWrkedMinutes.Text
                                        + "' ,dtLeaveStatus = 0 where recordno='"
                                        + lblRecrdNo.Text + "'";
                    sqlCmd1.CommandText = "update AttentanceReportMaster set Present = 1 , PresentText='Present' where recordno='"
                                      + lblRecrdNo.Text + "'"; 
                }
                else if (cmbStatus.SelectedIndex == 2) //Leave
                {
                    sqlCmd.CommandText = "update  DateWiseAttnData set dtDate='" + datePicker.Value.Date.ToString("MM/dd/yyyy HH:mm:ss")
                                         + "',dtHours= NULL,dtMinutes=NULL, dtFirstInTime=NULL"  
                                        + " ,dtLastOutTime=NULL"  
                                        + " ,dtOverTimeMin=NULL"  
                                        + ",dtLatenessMin=NULL" 
                                        + " ,dtTotWorkedMin=NULL"  
                                        + " ,dtLeaveStatus = 1 where recordno='"
                                        + lblRecrdNo.Text + "'";
                    sqlCmd1.CommandText = "update AttentanceReportMaster set Present = 2 , PresentText='Leave' where recordno='"
                                      + lblRecrdNo.Text + "'"; 
                }
                sqlCmd.Connection = sqlCon;
                sqlCon.Open();
                sqlCmd.ExecuteNonQuery();
                sqlCon.Close();
                sqlCmd1.Connection = sqlCon;
                sqlCon.Open();
                sqlCmd1.ExecuteNonQuery();
                sqlCon.Close();
            }
            else
            {
                EditGroupBox.Visible = false;
            }
        }

        private void cmbEmpid_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cmbEmpid.SelectedIndex != 0)
            {
                sqlCon.Open();
                sqlCmd.CommandText = "SELECT  EmpFirstName, EmpLastName FROM  EmployDetails where empid = " + cmbEmpid.SelectedValue;
                sqlCmd.CommandType = CommandType.Text;
                sqlCmd.Parameters.Clear();
                SqlDataReader reader;
                reader = sqlCmd.ExecuteReader();

                while (reader.Read())
                {
                    txtFirstName.Text = reader[0].ToString();
                    txtLastName.Text = reader[1].ToString();
                }
                sqlCon.Close();
            }
        }


        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Dispose();
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            EditGroupBox.Visible = false;
        }

            
    }
}
